﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using motelmanagement.common;
using System.Data;
using System.Data.OleDb;
namespace motelmanagement.dao
{
    class RoomDAO
    {
        SqlUtil sqlUtil = new SqlUtil();
        OleDbCommand cmd = null;
        private string strSql = null;

        public DataTable getAllRoom() {
            strSql = "SELECT Rooms.rooms_id, Rooms.rooms_name, Rooms.level_id";
            strSql+=", Level.level_name, Rooms.room_status_id, Rooms_status.room_status_name";
            strSql+=", Rooms.room_cate_id, Room_category.room_cate_name";
            strSql+=" FROM Room_category INNER JOIN (Rooms_status INNER JOIN ([Level]";
            strSql+=" INNER JOIN Rooms ON Level.level_id = Rooms.level_id)"; 
            strSql+=" ON Rooms_status.room_status_id = Rooms.room_status_id)";
            strSql+=" ON Room_category.room_cate_id = Rooms.room_cate_id";
            cmd = new OleDbCommand(strSql);
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public DataTable getRoom(int id)
        {
            strSql = "SELECT Rooms.rooms_id, Rooms.rooms_name, Rooms.level_id";
            strSql += ", Level.level_name, Rooms.room_status_id, Rooms_status.room_status_name";
            strSql += ", Rooms.room_cate_id, Room_category.room_cate_name";
            strSql += " FROM Room_category INNER JOIN (Rooms_status INNER JOIN ([Level]";
            strSql += " INNER JOIN Rooms ON Level.level_id = Rooms.level_id)";
            strSql += " ON Rooms_status.room_status_id = Rooms.room_status_id)";
            strSql += " ON Room_category.room_cate_id = Rooms.room_cate_id";
            strSql += " Where Rooms.rooms_id = @id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id",SqlDbType.Int).Value=id;
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public void insertRoom(string roomName, int levelId, int roomStatusId, int roomCateId) {
            strSql = "Insert Into Rooms(rooms_name,level_id,room_status_id,room_cate_id)";
            strSql += " Values(@name,@level,@stt,@cate)";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("name", SqlDbType.NText).Value = roomName;
            cmd.Parameters.Add("level", SqlDbType.Int).Value = levelId;
            cmd.Parameters.Add("stt", SqlDbType.Int).Value = roomStatusId;
            cmd.Parameters.Add("cate", SqlDbType.Int).Value = roomCateId;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void updateRoom(int roomId,string roomName, int levelId, int roomStatusId, int roomCateId)
        {
            strSql = "Update Rooms Set rooms_name = @name,level_id = @level,room_status_id = @stt,room_cate_id = @cate)";
            strSql += " Where rooms_id = @id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("name", SqlDbType.NText).Value = roomName;
            cmd.Parameters.Add("level", SqlDbType.Int).Value = levelId;
            cmd.Parameters.Add("stt", SqlDbType.Int).Value = roomStatusId;
            cmd.Parameters.Add("cate", SqlDbType.Int).Value = roomCateId;
            cmd.Parameters.Add("id", SqlDbType.Int).Value = roomId;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void deleteRoom(int id) {
            cmd = new OleDbCommand("Delete From Rooms Where rooms_id = @id");
            cmd.Parameters.Add("id",SqlDbType.Int).Value = id;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
    }
}
